﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Collections.Generic;

/// <summary>
/// Data access class that provides data access to the loans database
/// </summary>
public class LoanDAL
{
    /*data objects*/
    SqlConnection connection;
    SqlCommand cmd;

    public LoanDAL()
    {
        try
        {
            /*initilaize db variables*/
            connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["loanConnectionString"].ConnectionString);
            cmd = new SqlCommand("", connection);
        }
        catch
        {
            throw new Exception("Unable to connect to database");
        }
    }

    public bool OpenDbConnection()
    {
        /*check if command object is valid and has a valid connection*/
        if (cmd != null && cmd.Connection != null)
        {
            cmd = new SqlCommand("", connection);
        }

        /*check if connection is currently closed and open it*/
        if (cmd.Connection.State == ConnectionState.Closed)
        {
            try
            {
                cmd.Connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }/*check if connection is broken and close it then reopens it*/
        else if (cmd.Connection.State == ConnectionState.Broken)
        {
            try
            {
                cmd.Connection.Close();
                cmd.Connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }/*check if connection connecting(may occur from previos attempt to connect) and close it then reopens it*/
        else if (cmd.Connection.State == ConnectionState.Connecting)
        {
            try
            {
                cmd.Connection.Close();
                cmd.Connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }
        else /*else connection state is active*/
            return true;
    }

    public bool CloseDbConnection()
    {
        /*check if cmomman object is valid and has a valid connection*/
        if (cmd != null && cmd.Connection != null)
        {
            if (cmd.Connection.State != ConnectionState.Closed)
            {
                try
                {
                    cmd.Connection.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
                return true;
        }
        else
            return true;
    }


    /// <summary>
    /// Insert loan records into the loans database
    /// </summary>
    /// <param name="table">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records inserted</returns>
    public void BeginInsertAddonLoanAsync(DataTable loanRecordsTable)
    {

    }

    public UploadResult EndInsertAddonLoanAsync()
    {
        return new UploadResult();
    }


    /// <summary>
    /// Insert reducing balance loan records into the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records inserted</returns>
    public void BeginInsertReducingBalanceLoanAsync(DataTable loanRecordsTable)
    {

    }


    public UploadResult EndInsertReducingBalanceLoanAsync()
    {
        return new UploadResult();
    }

    /// <summary>
    /// Insert loan records into the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records inserted</returns>    
    public DataResponse<int?> InsertAddonLoan(Loan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertLoanAddon";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("start_date", SqlDbType.DateTime);
            cmd.Parameters.Add("principal", SqlDbType.Decimal);
            cmd.Parameters.Add("term", SqlDbType.Int);
            cmd.Parameters.Add("period", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("product", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("interest_rate", SqlDbType.Decimal);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;
        cmd.Parameters["start_date"].Value = loanRecord.StartDate;
        cmd.Parameters["principal"].Value = loanRecord.Principal;
        cmd.Parameters["term"].Value = loanRecord.Term;
        cmd.Parameters["period"].Value = loanRecord.Period;
        /*  -->*/
        cmd.Parameters["product"].Value = loanRecord.Product;
        cmd.Parameters["interest_rate"].Value = loanRecord.InterestRate;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);
                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorCode = recordError.Message;
                }
                else
                {
                    recordError.Message = "addon loan record did not insert";
                    response.ErrorMsg = recordError.Message;
                }

                /*setup loan record error details and logg error*/
                recordError.Date = DateTime.Now;
                recordError.Type = "LoanRecordError";
                /*convert error object to xml to be stored in the db*/
                recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
            }
        }
        catch (Exception e)
        {   /*re throw exception and cancel insert operation*/
            throw new Exception("Unable to insert addon loan record in database");
        }

        return response;
    }

    /// <summary>
    /// update loan records as closed in the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records that has been closed</returns>    
    public DataResponse<int?> InsertClosedAddonLoan(Loan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateClosedAddonLoan";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("closed_date", SqlDbType.DateTime);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;
        cmd.Parameters["closed_date"].Value = loanRecord.ClosedDate;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);

                    /*setup loan record error details and logg error*/
                    recordError.Date = DateTime.Now;
                    recordError.Type = "LoanRecordError";
                    /*convert error object to xml to be stored in the db*/
                    recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);

                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorMsg = recordError.Message;
                }
                else
                {
                    recordError.Message = "addon loan was not closed";
                    response.ErrorMsg = recordError.Message;
                }

            }
        }
        catch (Exception e)
        {   /*re throw exception and cancel insert operation*/
            throw e;
        }

        return response;
    }

    /// <summary>
    /// Insert loan records into the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>The number of addon loan records that has been closed</returns>    
    public DataResponse<int?> RemoveCancelledAddonLoan(Loan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RemoveCancelledAddonLoan";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);

                    /*setup loan record error details and logg error*/
                    recordError.Date = DateTime.Now;
                    recordError.Type = "LoanRecordError";
                    /*convert error object to xml to be stored in the db*/
                    recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);

                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorMsg = recordError.Message;
                }
                else
                {
                    recordError.Message = "addon loan was not cancelled";
                    response.ErrorMsg = recordError.Message;
                }
            }
        }
        catch (Exception e)
        {   /*re throw exception and cancel insert operation*/
            throw e;
        }

        return response;
    }

    /// <summary>
    /// Insert reducing balance loan records into the loans database
    /// </summary>
    /// <param name="table">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>The number of reducing balance loan records that has been remove because of cancellation</returns>
    public DataResponse<int?> InsertReducingBalanceLoan(ReducingBalanceLoan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertLoanRedBal";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("start_date", SqlDbType.DateTime);
            cmd.Parameters.Add("principal", SqlDbType.Decimal);
            cmd.Parameters.Add("term", SqlDbType.Int);
            cmd.Parameters.Add("period", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("product", SqlDbType.VarChar, 128);
            cmd.Parameters.Add("interest_rate", SqlDbType.Decimal);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;
        cmd.Parameters["start_date"].Value = loanRecord.StartDate;
        cmd.Parameters["principal"].Value = loanRecord.Principal;
        cmd.Parameters["term"].Value = loanRecord.Term;
        cmd.Parameters["period"].Value = loanRecord.Period;
        cmd.Parameters["product"].Value = loanRecord.Product;
        cmd.Parameters["interest_rate"].Value = loanRecord.InterestRate;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);
                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorCode = recordError.Message;
                }
                else
                {
                    recordError.Message = "reducing balance record did not insert";
                    response.ErrorMsg = recordError.Message;
                }

                /*setup loan record error details and logg error*/
                recordError.Date = DateTime.Now;
                recordError.Type = "LoanRecordError";
                /*convert error object to xml to be stored in the db*/
                recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);
            }
        }
        catch(Exception ex)
        {   /*re throw exception and cancel insert operation*/
            throw ex;
        }

        return response;
    }

    /// <summary>
    /// update loan records as closed in the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records that has been closed</returns>    
    public DataResponse<int?> InsertClosedReducingBalanceLoan(Loan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateClosedReducingBalanceLoan";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);
            cmd.Parameters.Add("closed_date", SqlDbType.DateTime);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;
        cmd.Parameters["closed_date"].Value = loanRecord.ClosedDate;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);

                    /*setup loan record error details and logg error*/
                    recordError.Date = DateTime.Now;
                    recordError.Type = "LoanRecordError";
                    /*convert error object to xml to be stored in the db*/
                    recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);

                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorMsg = recordError.Message;
                }
                else
                {
                    recordError.Message = "reducing balance loan was not closed";
                    response.ErrorMsg = recordError.Message;
                }

            }
        }
        catch (Exception e)
        {   /*re throw exception and cancel insert operation*/
            throw e;
        }

        return response;
    }

    /// <summary>
    /// Insert loan records into the loans database
    /// </summary>
    /// <param name="loanRecordsTable">data table containg loan records</param>
    /// <param name="listError">store errors generated by the insert operation</param>
    /// <returns>the number of loan records that has been remove because of cancellation</returns>    
    public DataResponse<int?> RemoveCancelledReducingBalanceLoan(Loan loanRecord, out Error recordError)
    {
        DataResponse<int?> response = new DataResponse<int?>();

        //loanRecord: used to store an error if the current record is faulty/invalid   
        //recordError: used to store the loan record details
        recordError = new Error();

        /*check if cmd object is already initialized, including its parameter list*/
        if (cmd.Parameters.Count == 0)
        {
            /*Set comman type adn text to stored procedure*/
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "RemoveCancelledReducingBalanceLoan";

            /*add the parameters for the stored procedures*/
            cmd.Parameters.Add("loan_no", SqlDbType.VarChar, 16);

            /*output parameters that are used to track errors that are generated in the stored procedure*/
            cmd.Parameters.Add("error_code", SqlDbType.Int);
            cmd.Parameters["error_code"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("error_msg", SqlDbType.VarChar, 512);
            cmd.Parameters["error_msg"].Direction = ParameterDirection.Output;
        }

        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["loan_no"].Value = loanRecord.LoanNo;

        try
        {
            /*check to ensure db connection is open before performing insert operation*/
            if (cmd.Connection.State != ConnectionState.Open)
                if (!OpenDbConnection())
                    throw new Exception("Unable to open db connection");

            /*execute command on db via command object and get the number of rows affected*/
            if (cmd.ExecuteNonQuery() > 0)
            {
                response.Result = 1;
            }
            else
            {
                /*check for db error code*/
                if (cmd.Parameters["error_code"].Value != DBNull.Value)
                {
                    recordError.Code = Convert.ToInt32(cmd.Parameters["error_code"].Value);
                    response.ErrorCode = Convert.ToString(recordError.Code);

                    /*setup loan record error details and logg error*/
                    recordError.Date = DateTime.Now;
                    recordError.Type = "LoanRecordError";
                    /*convert error object to xml to be stored in the db*/
                    recordError.AdditionalDetails = XMLWrite.Serialize(loanRecord);

                }
                /*check for db error message*/
                if (cmd.Parameters["error_msg"].Value != DBNull.Value)
                {
                    recordError.Message = cmd.Parameters["error_msg"].Value.ToString();
                    response.ErrorMsg = recordError.Message;
                }
                else
                {
                    recordError.Message = "reducing balance record was not cancelled";
                    response.ErrorMsg = recordError.Message;
                }
            }
        }
        catch (Exception e)
        {   /*re throw exception and cancel insert operation*/
            throw e;
        }
        return response;
    }

    public IEnumerable GetAddonLoansDetailedInterestProjection(DateTime fromReportDate, DateTime toReportDate, decimal interestRate)
    {
        /*Set comman type adn text to stored procedure*/
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "AddonTestV2";

        /*add the parameters for the stored procedures*/
        cmd.Parameters.Add("from_report_date", SqlDbType.DateTime);
        cmd.Parameters.Add("to_report_date", SqlDbType.DateTime);
        cmd.Parameters.Add("interest", SqlDbType.Decimal);



        /*add the value to the parameters for the stored procedures*/
        cmd.Parameters["from_report_date"].Value = fromReportDate;
        cmd.Parameters["to_report_date"].Value = toReportDate;
        cmd.Parameters["interest"].Value = interestRate;
        SqlDataReader reader;
        try
        {
        /*check to ensure db connection is open before performing insert operation*/
        if (cmd.Connection.State != ConnectionState.Open)
            if (!OpenDbConnection())
                throw new Exception("Unable to open db connection");

        /*execute command on db via command object and get */
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);    
        }
        catch(Exception ex)
        {   /*re throw exception and cancel insert operation*/
            throw ex;
        }
        return reader;
    }
}